{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Easy\n",
    "\n",
    "## Tables used in the musicians database\n",
    "\n",
    "band(**band_no**, band_name, band_home, band_type, b_date, band_contact)\n",
    "\n",
    "composer(**comp_no**, comp_is, comp_type)\n",
    "\n",
    "composition(**c_no**, comp_date, c_title, c_in)\n",
    "\n",
    "concert(**concert_no**, concert_venue, concert_in, con_date, concert_orgniser)\n",
    "\n",
    "has_composed(**_cmpr_no_**, **_cmpn_no_**)\n",
    "\n",
    "musician(**m_no**, m_name, born, died, born_in, living_in)\n",
    "\n",
    "performance(**_pfrmnc_no_**, gave, performed, conducted_by, performed_in)\n",
    "\n",
    "performer(**perf_no**, perf_is, instrument, perf_type)\n",
    "\n",
    "place(**place_no**, place_town, place_country)\n",
    "\n",
    "plays_in(**_player_**, **band_id**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **musician**\n",
    "\n",
    "m_no | m_name | born | died | born_in | living_in\n",
    "-:|-----------|------|------|--------:|-----:\n",
    "1 | Fred Bloggs | 02/01/48 |  | 1 | 2\n",
    "2 | John Smith | 03/03/50 |  | 3 | 4\n",
    "3 | Helen Smyth | 08/08/48 |  | 4 | 5\n",
    "4 | Harriet Smithson | 09/05/1909 | 20/09/80 | 5 | 6\n",
    "5 | James First | 10/06/65 |  | 7 | 7\n",
    "... | | | | | \n",
    "\n",
    "- **place**\n",
    "\n",
    "place_no | place_town | place_country\n",
    "--------:|------------|---------\n",
    "1 | Manchester | England\n",
    "2 | Edinburgh | Scotland\n",
    "3 | Salzburg | Austria\n",
    "4 | New York | USA\n",
    "5 | Birmingham | England\n",
    "... | | \n",
    "\n",
    "- **performer**\n",
    "\n",
    "perf_no | perf_is | instrument | perf_type\n",
    "-:|--:|--------|---------------------\n",
    "1 | 2 | violin | classical\n",
    "2 | 4 | viola | classical\n",
    "3 | 6 | banjo | jazz\n",
    "4 | 8 | violin | classical\n",
    "5 | 12 | guitar | jazz\n",
    "...| | |\n",
    "\n",
    "- **composer**\n",
    "\n",
    "comp_no | comp_is | comp_type\n",
    "-:|--:|----------------\n",
    "1 | 1 | jazz\n",
    "2 | 3 | classical\n",
    "3 | 5 | jazz\n",
    "4 | 7 | classical\n",
    "5 | 9 | jazz\n",
    "... | |\n",
    "\n",
    "- **band**\n",
    "\n",
    "band_no | band_name | band_home | band_type | b_date | band_contact\n",
    "-:|-----|--:|-----------|----------|----:\n",
    "1 | ROP | 5 | classical | 30/01/01 | 11\n",
    "2 | AASO | 6 | classical |  | 10\n",
    "3 | The J Bs | 8 | jazz |  | 12\n",
    "4 | BBSO | 9 | classical |  | 21\n",
    "5 | The left Overs | 2 | jazz |  | 8\n",
    "... | | | | |\n",
    "\n",
    "- **plays_in**\n",
    "\n",
    "player | band_id\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 7\n",
    "3 | 1\n",
    "4 | 1\n",
    "4 | 7\n",
    "... |\n",
    "\n",
    "- **composition**\n",
    "\n",
    "c_no | comp_date | c_title | c_in\n",
    "-:|----------|--------|-------:\n",
    "1 | 17/06/75 | Opus 1 | 1\n",
    "2 | 21/07/76 | Here Goes | 2\n",
    "3 | 14/12/81 | Valiant Knight | 3\n",
    "4 | 12/01/82 | Little Piece | 4\n",
    "5 | 13/03/85 | Simple Song | 5\n",
    "... | | |\n",
    "\n",
    "- **has_composed**\n",
    "\n",
    "cmpr_no | cmpn_no\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 8\n",
    "2 | 11\n",
    "3 | 2\n",
    "3 | 13\n",
    "... |\n",
    "\n",
    "- **concert**\n",
    "\n",
    "concert_no | concert_venue | concert_in | con_date | concert_orgniser\n",
    "-:|------------------|--:|----------|----:\n",
    "1 | Bridgewater Hall | 1 | 06/01/95 | 21\n",
    "2 | Bridgewater Hall | 1 | 08/05/96 | 3\n",
    "3 | Usher Hall | 2 | 03/06/95 | 3\n",
    "4 | Assembly Rooms | 2 | 20/09/97 | 21\n",
    "5 | Festspiel Haus | 3 | 21/02/95 | 8\n",
    "... | | | |\n",
    "\n",
    "- **performance**\n",
    "\n",
    "pfrmnc_no | gave | performed | conducted_by | performed_in\n",
    "-:|--:|--:|---:|-----------------------:\n",
    "1 | 1 | 1 | 21 | 1\n",
    "2 | 1 | 3 | 21 | 1\n",
    "3 | 1 | 5 | 21 | 1\n",
    "4 | 1 | 2 | 1 | 2\n",
    "5 | 2 | 4 | 21 | 2\n",
    "... | | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the data model of the musician tables\n",
    "\n",
    "![](../src/img/musician_str.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "\n",
    "sc = (SparkSession.builder.appName('app16-1') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "composer = sc.read.table('sqlzoo.composer')\n",
    "composition = sc.read.table('sqlzoo.composition')\n",
    "concert = sc.read.table('sqlzoo.concert')\n",
    "has_composed = sc.read.table('sqlzoo.has_composed')\n",
    "musician = sc.read.table('sqlzoo.musician')\n",
    "performance = sc.read.table('sqlzoo.performance')\n",
    "performer = sc.read.table('sqlzoo.performer')\n",
    "place = sc.read.table('sqlzoo.place')\n",
    "plays_in = sc.read.table('sqlzoo.plays_in')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the organiser's name of the concert in the Assembly Rooms after the first of Feb, 1997.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James Steeple</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          m_name\n",
       "0  James Steeple"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.filter((concert['concert_venue']=='Assembly Rooms') & \n",
    "                (concert['con_date']>'1997-02-01'))\n",
    " .join(musician, on=(concert['concert_orgniser']==musician['m_no']))\n",
    " .select('m_name')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Find all the performers who played guitar or violin and were born in England.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alan Fluff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Davis Heavan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harriet Smithson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Forte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>James First</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Theo Mengel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             m_name\n",
       "0        Alan Fluff\n",
       "1      Davis Heavan\n",
       "2  Harriet Smithson\n",
       "3       Harry Forte\n",
       "4       James First\n",
       "5       Theo Mengel"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(performer.filter(performer['instrument'].isin(['violin', 'guitar']))\n",
    " .join(musician, on=(performer['perf_is']==musician['m_no']))\n",
    " .join(place.filter(place['place_country']=='England'),\n",
    "       on=(musician['born_in']==place['place_no']))\n",
    " .select('m_name')\n",
    " .orderBy('m_name')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of musicians who have conducted concerts in USA together with the towns and dates of these concerts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>place_town</th>\n",
       "      <th>con_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James Steeple</td>\n",
       "      <td>New York</td>\n",
       "      <td>1997-06-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          m_name place_town    con_date\n",
       "0  James Steeple   New York  1997-06-15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.join(place.filter(place['place_country']=='USA'),\n",
    "              on=(concert['concert_in']==place['place_no']))\n",
    " .join(performance, on=(concert['concert_no']==performance['performed_in']))\n",
    " .join(musician, on=(performance['conducted_by']==musician['m_no']))\n",
    " .select('m_name', 'place_town', 'con_date')\n",
    " .distinct()\n",
    " .orderBy('m_name')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**How many concerts have featured at least one composition by Andy Jones? List concert date, venue and the composition's title.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>con_date</th>\n",
       "      <th>concert_venue</th>\n",
       "      <th>c_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-05-08</td>\n",
       "      <td>Bridgewater Hall</td>\n",
       "      <td>A Simple Piece</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     con_date     concert_venue         c_title\n",
       "0  1996-05-08  Bridgewater Hall  A Simple Piece"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.join(composition, on=(concert['concert_no']==composition['c_in']))\n",
    " .join(has_composed, on=(composition['c_no']==has_composed['cmpn_no']))\n",
    " .join(composer, on=(has_composed['cmpr_no']==composer['comp_no']))\n",
    " .join(musician.filter(musician['m_name']=='Andy Jones'), \n",
    "       on=(composer['comp_is']==musician['m_no']))\n",
    " .select('con_date', 'concert_venue', 'c_title')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**List the different instruments played by the musicians and avg number of musicians who play the instrument.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>instrument</th>\n",
       "      <th>Alan Fluff</th>\n",
       "      <th>Davis Heavan</th>\n",
       "      <th>Elsie James</th>\n",
       "      <th>Harriet Smithson</th>\n",
       "      <th>Harry Forte</th>\n",
       "      <th>Helen Smyth</th>\n",
       "      <th>James First</th>\n",
       "      <th>James Quick</th>\n",
       "      <th>Jeff Dawn</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Louise Simpson</th>\n",
       "      <th>Sue Little</th>\n",
       "      <th>Theo Mengel</th>\n",
       "      <th>ALL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALL</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>banjo</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bass</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cello</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>clarinet</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>cornet</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>drums</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>flute</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>guitar</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>horn</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>trombone</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>trumpet</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>viola</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>violin</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   instrument  Alan Fluff  Davis Heavan  Elsie James  Harriet Smithson  \\\n",
       "0         ALL           2             3            3                 2   \n",
       "1       banjo           0             0            0                 0   \n",
       "2        bass           0             0            0                 0   \n",
       "3       cello           0             1            1                 0   \n",
       "4    clarinet           0             0            0                 0   \n",
       "5      cornet           0             0            0                 0   \n",
       "6       drums           0             0            0                 0   \n",
       "7       flute           0             0            0                 0   \n",
       "8      guitar           0             1            1                 0   \n",
       "9        horn           0             0            0                 0   \n",
       "10   trombone           0             0            0                 0   \n",
       "11    trumpet           0             0            0                 0   \n",
       "12      viola           1             1            1                 1   \n",
       "13     violin           1             0            0                 1   \n",
       "\n",
       "    Harry Forte  Helen Smyth  James First  James Quick  Jeff Dawn  John Smith  \\\n",
       "0             3            1            1            2          2           3   \n",
       "1             0            0            0            0          0           0   \n",
       "2             0            0            0            0          1           1   \n",
       "3             0            0            0            0          0           0   \n",
       "4             1            0            0            0          0           0   \n",
       "5             0            0            0            0          0           0   \n",
       "6             1            0            0            0          0           0   \n",
       "7             0            0            0            1          0           1   \n",
       "8             0            0            0            0          0           0   \n",
       "9             0            1            0            0          0           0   \n",
       "10            0            0            0            0          0           0   \n",
       "11            0            0            0            1          0           0   \n",
       "12            0            0            0            0          0           0   \n",
       "13            1            0            1            0          1           1   \n",
       "\n",
       "    Louise Simpson  Sue Little  Theo Mengel  ALL  \n",
       "0                3           1            3   29  \n",
       "1                0           0            1    1  \n",
       "2                1           0            0    3  \n",
       "3                0           1            0    3  \n",
       "4                0           0            0    1  \n",
       "5                1           0            0    1  \n",
       "6                0           0            1    2  \n",
       "7                0           0            0    2  \n",
       "8                0           0            0    2  \n",
       "9                0           0            0    1  \n",
       "10               1           0            0    1  \n",
       "11               0           0            0    1  \n",
       "12               0           0            0    4  \n",
       "13               0           0            1    7  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (performer.join(musician, on=(performer['perf_is']==musician['m_no'])))\n",
    "(t.groupBy('instrument')\n",
    " .pivot('m_name')\n",
    " .agg(count('m_no'))\n",
    " .union(t.withColumn('instrument', lit('ALL'))\n",
    "        .groupBy('instrument')\n",
    "        .pivot('m_name')\n",
    "        .agg(count('m_no')))\n",
    " .join(t.groupBy('instrument')\n",
    "       .agg(count('m_no').alias('ALL'))\n",
    "       .union(t.withColumn('instrument', lit('ALL'))\n",
    "              .groupBy('instrument')\n",
    "              .agg(count('m_no').alias('ALL'))),\n",
    "       on='instrument')\n",
    " .orderBy('instrument')\n",
    " .fillna(0)\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
